/*
	Copyright (c) 2010 Canadensys
*/
package net.canadensys.dataportal.vascan;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import net.canadensys.dataportal.utils.HibernateUtil;
import net.canadensys.dataportal.vascan.config.ApplicationConfig;

import org.hibernate.HibernateException;
import org.hibernate.Query;


public final class LookupManager {
	
	public static ResultSet getDistributions(boolean returnCountOnly, int limitResultsTo, String combination, String habitus, int taxonid, String[] province, String[] status, String[] rank, String hybrids, String sort){
		org.hibernate.Session hibernateSession = HibernateUtil.getSessionFactory().getCurrentSession();
		ResultSet rs = null;
		String tables = "lookup";
		String sortJoin = "1";
		
		// don't go any further if no provinces & status
		// Make sure this request is looking for something
		if((status == null || province == null) && taxonid == 0 && habitus.equals("all"))
			return rs;
		
		StringBuffer sql = new StringBuffer("");
		
		if(returnCountOnly)
			sql.append("SELECT count(lookup.taxonid)");
		else
			sql.append("SELECT lookup.*");
		
		sql.append("\nFROM ").append(tables);
		sql.append("\nWHERE ").append(sortJoin);
		
		// filter by hierarchy of children for IN clause
		if(taxonid > 0){
			StringBuffer in = new StringBuffer(taxonid);
			taxonClause(taxonid,in);
			sql.append("\nAND lookup.taxonid IN ( ").append(taxonid).append(in.toString()).append(")");
		}
		
		// filter by status in provinces
		if(status != null && province != null && combination != null)
			sql.append("\nAND ").append(provinceClause(combination, status, province));
		
		
		// filter by habitus
		if(habitus != null && habitus!="" && !habitus.equals("all"))
			sql.append("\nAND ").append(habitusClause(habitus));
		
		// filter by rank
		if(rank != null){
			sql.append("\nAND ").append(rankClause(rank));
		}
		
		// filter by hybrids
		if(!(Boolean.TRUE.toString().equalsIgnoreCase(hybrids)))
			sql.append("\nAND ").append(hybridClause());
		
		// order
		sql.append("\n").append(sortClause(sort));
		
		// limit
		if(limitResultsTo > 0 )
			sql.append("\n").append(limitClause(limitResultsTo));

		try {
			Statement statement = hibernateSession.connection().createStatement(ResultSet.CONCUR_READ_ONLY,ResultSet.TYPE_FORWARD_ONLY);
			rs = statement.executeQuery(sql.toString());
			
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Guilty SQL:" + sql.toString());
		}		
		
		return rs;
	}
	
	private static String limitClause(int limitResultsTo){
		String clause = "";
		clause = "LIMIT ".concat(String.valueOf(limitResultsTo));
		return clause;
	}
	
	/**
	 * TODO Issue 753
	 * @param sort
	 * @return
	 */
	private static String sortClause(String sort){
		String clause = "";
		if(sort != null){
			if(sort.equals("alphabetically")){
				clause = "ORDER BY calname ASC";
			}
			else if(sort.equals("taxonomically")){
				clause = "";
			}
			else{
				
			}
		}
		return clause;
	}
	
	/**
	 * This method is used to exclude hybrids
	 * @return
	 */
	private static String hybridClause(){
		String clause = "lookup.calname NOT LIKE '%×%'";// not x character, × = &times;
		return clause;
	}

	
	/**
	 * 
	 * fill "in" StringBuffer fro sql statement : where taxonid IN (133,455,553,1,43)
	 * 
	 * @param taxonid
	 * @param in
	 */
	private static void taxonClause(int taxonid, StringBuffer in){
		org.hibernate.Session hibernateSession = HibernateUtil.getSessionFactory().getCurrentSession();
		Statement statement;
		try {
			statement = hibernateSession.connection().createStatement(ResultSet.CONCUR_READ_ONLY,ResultSet.TYPE_FORWARD_ONLY);
			String sql = "SELECT childid FROM taxonomy,taxon WHERE parentid = " + taxonid + " AND taxonomy.childid = taxon.id AND taxon.statusid = " + ApplicationConfig.STATUS_ACCEPTED;
			ResultSet rs = statement.executeQuery(sql.toString());
			if(rs != null){
				rs.beforeFirst();
				while(rs.next()){
					int id = rs.getInt("childid");
					in.append(",").append(String.valueOf(id));
					taxonClause(id,in);
				}
				rs.close();
				rs = null;
			}
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	private static String rankClause(String[] rank){
		StringBuffer buffer = new StringBuffer("");
		ArrayList<String> clause = new ArrayList<String>();
		ArrayList<String> validSqlRanks = new ArrayList<String>();

		
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_CLASS);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_SUBCLASS);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_ORDER);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_SUPERORDER);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_FAMILY);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_SUBFAMILY);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_SUBTRIBE);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_TRIBE);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_GENUS);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_SUBGENUS);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_SECTION);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_SUBSECTION);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_SERIES);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_SPECIES);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_SUBSPECIES);
		validSqlRanks.add(ApplicationConfig.LABEL_RANK_VARIETY);
		
		for(String r : rank){
			buffer = new StringBuffer(" (");
			//prevent sql injection
			if(validSqlRanks.contains(r.toLowerCase()))
				clause.add("lookup.rank = '" + r.toLowerCase() + "'");
		}
		Iterator it = clause.iterator();
        if (it.hasNext()) {
            buffer.append(it.next());
            while (it.hasNext()) {
            	buffer.append(" OR ");
                buffer.append(it.next());
            }
        }	
        buffer.append(") ");
		return buffer.toString();
	}
	
	private static String habitusClause(String habitus){
		String clause = "1";
		ArrayList<String> validSqlHabitus = new ArrayList<String>();
		validSqlHabitus.add(ApplicationConfig.LABEL_HABITUS_HERB);
		validSqlHabitus.add(ApplicationConfig.LABEL_HABITUS_TREE);
		validSqlHabitus.add(ApplicationConfig.LABEL_HABITUS_SHRUB);
		validSqlHabitus.add(ApplicationConfig.LABEL_HABITUS_VINE);
		if(validSqlHabitus.contains(habitus))
			clause = "lookup.calhabit like '%" + habitus + "%'";
		return clause;
	}
	
	/**
	 * Build sql statement to filter on provinces and statuses; String[] status,
	 * String[] province & String combination must not be null.
	 * 
	 * depending on the combination value (allof, anyof, only, only_ca), the sql
	 * operator will change from AND to OR to match groupings of provinces and
	 * statuses. The only / only_ca combination will also append exclusion sql
	 * statements to exlude statuses from provinces
	 * 
	 * ex. (allof), (native,ephemere), (qc,on): 
	 * AND (p_ON = 'native' OR p_ON = 'ephemere') AND (p_QC = 'native' OR p_QC = 'ephemere') ...
	 * 
	 * ex. (anyof), (native,ephemere), (qc,on): 
	 * AND (p_ON = 'native' OR p_ON = 'ephemere' OR p_QC = 'native' OR p_QC = 'ephemere') ...
	 * 
	 * ex. (only), (native,ephemere), (qc,on):
	 * AND (p_ON = 'native' OR p_ON = 'ephemere') OR (p_QC = 'native' OR p_QC = 'ephemere') 
	 * AND p_BC <> 'native' AND p_BC <> 'ephemere' AND p_PM <> 'native' AND p_PM <> 'ephemere' [and so on for all provinces & territories] ... 
	 * 
	 * ex. (only_ca), (native,ephemere), (qc,on):
	 * AND (p_ON = 'native' OR p_ON = 'ephemere') OR (p_QC = 'native' OR p_QC = 'ephemere') 
	 * AND p_BC <> 'native' AND p_BC <> 'ephemere' AND p_PM <> 'native' AND p_MB <> 'ephemere' [and so on for all provinces & territories, except p_PM & p_GL] ...
	 * 
	 * 
	 * @param combination
	 * @param status
	 * @param province
	 * @return a string representation of the sql statement AND ...
	 */
	private static String provinceClause(String combination, String[] status, String[] province){
		StringBuffer buffer = new StringBuffer("");
		Iterator it;
		ArrayList<String> clauses = new ArrayList<String>();
		ArrayList<String> clause = new ArrayList<String>();
		String op = "";
		
		// all available provinces & territories 
		String provinces[] = ApplicationConfig.ALL_PROVINCES;
		
		// set sql operator based on type of combination 
		if(combination.equals("allof"))
			op = " AND ";
		else if(combination.equals("anyof"))
			op = " OR ";
		else if(combination.equals("only"))
			op = " AND ";
		else{ //only_ca
			op = " AND ";
			// we are looking to query only canadian provinces, and ignore greenland and saint-pierre miquelon 
			// (may or may not have a status that must be excluded in other canadian provinces)
			provinces = ApplicationConfig.CANADA_PROVINCES;
		}
		
		// INCLUSION SQL STATEMENT (province = status)
		// for every provinces checked in the checklist builder
		for(String prov : province){
			clause = new ArrayList<String>();
			buffer = new StringBuffer("(");
			
			// match against all the statuses checked in the checklist builder
			// fill an array list of clauses that will be joined into a string by an sql operator
			for(String stat: status){
				clause.add("lookup.`" + prov + "` = '" + stat +"'");
			}
			
			// for all the province = status clauses, join into a string with appropriate operator
			it = clause.iterator();
	        if (it.hasNext()) {
	            buffer.append(it.next());
	            while (it.hasNext()) {
	            	if(combination.equals("allof") && status.length == 1)
	            		buffer.append(" AND ");
	            	else
	            		buffer.append(" OR ");
	                buffer.append(it.next());
	            }
	        }
			buffer.append(")");	
			clauses.add(buffer.toString());
		}

		// EXCLUSION SQL STATEMENT (province <> status)
		if(combination.equals("only") || combination.equals("only_ca")){
			// we need to exclude every other province...
			
			ArrayList<String> notEqualClause = new ArrayList<String>();
			// for every checked status
			for(String stat: status){
				// for every province
				for(String excludeProvince : provinces){
					boolean exclude = true;
					// if province is checked in checklist builder, do not exclude
					for(String prov : province){
						if(prov.equals(excludeProvince)){
							exclude = false;
							break;
						}
					}
					// if province is not checked in builder, exclude that province for that status
					if(exclude){
						notEqualClause.add("lookup.`" + excludeProvince + "` <> '" + stat +"'");
					}
				}
			}
			
			//for all the province <> status clauses, join into a string with AND operator (p_ON <> 'native' AND p_QC <> 'native' AND p_SK <> 'native' ...)
			StringBuffer notEqualBuffer = new StringBuffer("");
			it = notEqualClause.iterator();
	        if (it.hasNext()) {
	            notEqualBuffer.append(it.next());
	            while (it.hasNext()) {
	            	notEqualBuffer.append(" AND ");
	            	notEqualBuffer.append(it.next());
	            }
	        }

	        // reuse the previous clauses arraylist to rewrite the inclusion sql statement with proper parenthesis groupings and OR operators
	        StringBuffer equalBuffer = new StringBuffer("(");
	        it = clauses.iterator();
	        if (it.hasNext()) {
	            equalBuffer.append(it.next());
	            while (it.hasNext()) {
	            	equalBuffer.append(" OR ");
	            	equalBuffer.append(it.next());
	            }
	        }
	        equalBuffer.append(")");
	        
	        // rewrite clauses to add only to sql statements : the equality one where we match provinces on statuses 
	        // and the exclusion one where we exclude provinces on statuses. these two statements will be joined by an
	        // AND operator
	        clauses = new ArrayList();
	        clauses.add(equalBuffer.toString());
			clauses.add(notEqualBuffer.toString());
		}
		
		// join all sql statement clauses with sql operator
		buffer = new StringBuffer("(");
		it = clauses.iterator();
	    if (it.hasNext()) {
	        buffer.append(it.next());
	        while (it.hasNext()) {
	            buffer.append(op);
	            buffer.append(it.next());
	        }
	    }
		buffer.append(")");
		return buffer.toString();
	}
	
	/**
	 * This method will delete all the item contain in the lookup table but will not commit.
	 */
	public static void deleteLookupTable(org.hibernate.Session hibernateSession)
		throws HibernateException{
		Query query = hibernateSession.createSQLQuery("DELETE FROM lookup");
		query.executeUpdate();
	}
}
